County Labor Analysis and Salary Statistics (CLASS)
PI: Sulchan Yoon
2023-05-10
Data 205 Montgomery College
Data Montgomery Employee Salaries From 2019 - 2022
Summary of Dataset
At the end of 2019, a new viral bacteria took the world by surprise. Unrealized by the world until mid 2020, this virus has made nations rethink how to handle technology, production, policies, medical emergencies, and handling of the economy. Disruptions in the production of common goods have been shot due to policies requiring workers to stay home. Medical researchers to find cures and a policy to allow rapid testing and approvals for mass production was limit tested. A lack of goods brought rare metal productions to a halt, causing technology advancements, including cars, computers, phones, even as simple as a microwave, to be highly limited. With a lack of production, rules requiring distancing, fear of a highly contagious virus spreading around mom-and-pop stores, the global economy was shut down.
Small private shops did what they could to keep afloat, corporations pushed for teleworking options, hospitals were pushed to their limits, and many non-critical government agencies were forced to minimalize their work force to support the global movement on stopping Corona Virus from spreading.
Talk about Covid major dates for timeline….. (Part of final)
Data Montgomery has provided a standardized report of employee salaries for many consecutive years. We will be taking a look into the 2019 to 2022 to find trends in overall employment for Montgomery county, look at how wages have been impacted from Covid’s economic impact, and see if the lack of employment has had any major impact to the overtime usage.
Definition of Labels
| Column Information | Type | Description |
|---|---|---|
| Department | chr | Department Code |
| Department Name | chr | Name of the Department |
| Division | chr | Division including division code |
| Gender | chr | Gender of employee |
| Base Salary | num | Rate of pay for the given employee |
| Overtime Pay | num | Amount of overtime received in the year |
| Longevity Pay | num | Amount of Longevity pay recieved in the year |
| Grade | chr | Salary grade for the given employee |
Questions?
General questions: There seems to be some departments with large pay gaps, can we find potential causes?
Has overtime pay overstayed it’s welcome?
How does gender play a role in salary statistics for Montgomery County?
Are we getting to the point where job statistics are getting back to pre-covid events?
Initiate
pacman::p_load(tidyverse,dplyr,readxl,plotly,ggthemes, viridis,RColorBrewer)
setwd("~/Data Study/Data 205 MC")Load data
db2019 <- read.csv("Employee_Salaries_2019.csv")
db2020 <- read.csv("Employee_Salaries_2020.csv")
db2021 <- read.csv("Employee_Salaries_2021.csv")
db2022 <- read.csv("Employee_Salaries_2022.csv")Initial Review
str(db2019)## 'data.frame': 10105 obs. of 8 variables:
## $ Department : chr "BOA" "BOA" "BOA" "BOE" ...
## $ Department.Name : chr "Board of Appeals Department" "Board of Appeals Department" "Board of Appeals Department" "Board of Elections" ...
## $ Division : chr "Board of Appeals Division" "Board of Appeals Division" "Executive Director" "Director" ...
## $ Gender : chr "F" "F" "F" "F" ...
## $ Base.Salary : num 78902 58482 144751 183654 62488 ...
## $ X2019.Overtime.Pay : num 0 0 0 0 0 0 0 0 0 0 ...
## $ X2019.Longevity.Pay: num 0 0 0 0 0 ...
## $ Grade : chr "18" "16" "M3" "M1" ...colSums(is.na(db2019))## Department Department.Name Division Gender
## 0 0 0 0
## Base.Salary X2019.Overtime.Pay X2019.Longevity.Pay Grade
## 0 0 0 0str(db2020)## 'data.frame': 9958 obs. of 8 variables:
## $ Department : chr "ABS" "ABS" "ABS" "ABS" ...
## $ Department.Name : chr "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" ...
## $ Division : chr "Wholesale Administration" "Administrative Services" "Administration" "Wholesale Operations" ...
## $ Gender : chr "F" "F" "M" "F" ...
## $ Base.Salary : num 78902 35926 167345 90848 78902 ...
## $ X2020.Overtime.Pay : num 199 0 0 0 205 ...
## $ X2020.Longevity.Pay: num 0 4039 0 5718 2460 ...
## $ Grade : chr "18" "16" "M2" "21" ...colSums(is.na(db2020))## Department Department.Name Division Gender
## 0 0 0 0
## Base.Salary X2020.Overtime.Pay X2020.Longevity.Pay Grade
## 0 0 0 0str(db2021)## 'data.frame': 9907 obs. of 8 variables:
## $ Department : chr "ABS" "ABS" "ABS" "ABS" ...
## $ Department.Name : chr "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" ...
## $ Division : chr "Beer Loading" "Liquor and Wine Delivery Operations" "Beer Delivery Operations" "Beer Delivery Operations" ...
## $ Gender : chr "M" "M" "M" "M" ...
## $ Base.Salary : num 87969 80086 80086 70814 76419 ...
## $ X2021.Overtime.Pay : num 32953 32656 31369 29838 21379 ...
## $ X2021.Longevity.Pay: num NA 1105 1591 NA NA ...
## $ Grade : chr "20" "18" "18" "18" ...colSums(is.na(db2021))## Department Department.Name Division Gender
## 0 0 0 0
## Base.Salary X2021.Overtime.Pay X2021.Longevity.Pay Grade
## 0 4312 7494 0str(db2022)## 'data.frame': 10052 obs. of 8 variables:
## $ Department : chr "ZAH" "ZAH" "ZAH" "ZAH" ...
## $ Department_Name: chr "Office of Zoning and Administrative Hearings" "Office of Zoning and Administrative Hearings" "Office of Zoning and Administrative Hearings" "Office of Zoning and Administrative Hearings" ...
## $ Division : chr "ZAH 05 Zoning and Administrative Hearings" "ZAH 05 Zoning and Administrative Hearings" "ZAH 05 Zoning and Administrative Hearings" "ZAH 05 Office of Zoning and Administrative Hearings" ...
## $ Gender : chr "M" "F" "F" "F" ...
## $ Base.Salary : num 142100 162000 93895 77180 196632 ...
## $ Overtime.Pay : num NA NA NA NA NA NA NA NA NA NA ...
## $ Longevity.Pay : num NA NA NA NA NA NA NA NA NA NA ...
## $ Grade : chr "N35" "N35" "N21" "N18" ...colSums(is.na(db2022))## Department Department_Name Division Gender Base.Salary
## 0 0 0 0 0
## Overtime.Pay Longevity.Pay Grade
## 4266 7653 0General Data Cleanup
db2019 <- db2019 %>%
rename("Overtime" = "X2019.Overtime.Pay",
"Longevity" = "X2019.Longevity.Pay",
"Department Name" = "Department.Name",
"Base Salary" = "Base.Salary") %>%
mutate("Year" = 2019)
db2020 <- db2020 %>%
rename("Overtime" = "X2020.Overtime.Pay",
"Longevity" = "X2020.Longevity.Pay",
"Department Name" = "Department.Name",
"Base Salary" = "Base.Salary") %>%
mutate("Year" = 2020)
db2021 <- db2021 %>%
rename("Overtime" = "X2021.Overtime.Pay",
"Longevity" = "X2021.Longevity.Pay",
"Department Name" = "Department.Name",
"Base Salary" = "Base.Salary") %>%
mutate("Year" = 2021)
db2022 <- db2022 %>%
rename("Overtime" = "Overtime.Pay",
"Longevity" = "Longevity.Pay",
"Department Name" = "Department_Name",
"Base Salary" = "Base.Salary") %>%
mutate("Year" = 2022)db2019$Overtime <- sub(0,NA,db2019$Overtime)
db2020$Overtime <- sub(0,NA,db2020$Overtime)
db2021$Overtime <- sub(0,NA,db2021$Overtime)
db2022$Overtime <- sub(0,NA,db2022$Overtime)
db2019$Longevity <- sub(0,NA,db2019$Longevity)
db2020$Longevity <- sub(0,NA,db2020$Longevity)
db2021$Longevity <- sub(0,NA,db2021$Longevity)
db2022$Longevity <- sub(0,NA,db2022$Longevity)
colSums(is.na(db2019))## Department Department Name Division Gender Base Salary
## 0 0 0 0 0
## Overtime Longevity Grade Year
## 6321 8494 0 0colSums(is.na(db2020))## Department Department Name Division Gender Base Salary
## 0 0 0 0 0
## Overtime Longevity Grade Year
## 6233 8485 0 0colSums(is.na(db2021))## Department Department Name Division Gender Base Salary
## 0 0 0 0 0
## Overtime Longevity Grade Year
## 6252 8416 0 0colSums(is.na(db2022))## Department Department Name Division Gender Base Salary
## 0 0 0 0 0
## Overtime Longevity Grade Year
## 6344 8340 0 0EDA
Through our EDA process, we are looking to find hints to answer our initial prompts. We are also looking to find information that will support defining what the new “pre-covid state” would look like.
2019 Employee Salaries Review
str(db2019)## 'data.frame': 10105 obs. of 9 variables:
## $ Department : chr "BOA" "BOA" "BOA" "BOE" ...
## $ Department Name: chr "Board of Appeals Department" "Board of Appeals Department" "Board of Appeals Department" "Board of Elections" ...
## $ Division : chr "Board of Appeals Division" "Board of Appeals Division" "Executive Director" "Director" ...
## $ Gender : chr "F" "F" "F" "F" ...
## $ Base Salary : num 78902 58482 144751 183654 62488 ...
## $ Overtime : chr NA NA NA NA ...
## $ Longevity : chr NA NA NA NA ...
## $ Grade : chr "18" "16" "M3" "M1" ...
## $ Year : num 2019 2019 2019 2019 2019 ...options(scipen = 999)
plot2019 <- ggplot(db2019, aes(`Department`, `Base Salary`))+
geom_boxplot() +
scale_fill_viridis(discrete = TRUE, alpha=0.6) +
theme(axis.text.x = element_text(angle = 45),
legend.position="none",
plot.title = element_text(size=11)
) +
ggtitle("2019 Salaries by Department") +
xlab("Department")
ggplotly(plot2019)stat2019 <- db2019 %>%
group_by(Department) %>%
summarise("Total Salaries" = sum(`Base Salary`,na.rm=T),
"Average Salaries" = mean(`Base Salary`,na.rm=T),
"Count of Data Records" = n(),
"Lowest Salary" = min(`Base Salary`,na.rm=T),
"Highest Salary" = max(`Base Salary`,na.rm=T),
"Average Overtime Pay" = mean(`Overtime`, na.rm=T)) %>%
mutate("Salary Gap" = `Highest Salary` - `Lowest Salary`)
head(stat2019)## # A tibble: 6 × 8
## Department `Total Salaries` Average …¹ Count…² Lowes…³ Highe…⁴ Avera…⁵ Salar…⁶
## <chr> <dbl> <dbl> <int> <dbl> <dbl> <dbl> <dbl>
## 1 BOA 282135. 94045. 3 58482. 144751 NA 86269.
## 2 BOE 2387357. 79579. 30 37506 183654. NA 146148.
## 3 CAT 9127798 115542. 79 20470. 210143 NA 189673.
## 4 CCL 10297327. 100954. 102 31441. 206000 NA 174559.
## 5 CEC 5816075. 70073. 83 20718. 186735. NA 166017.
## 6 CEX 5598448. 124410. 45 16732. 303091 NA 286359.
## # … with abbreviated variable names ¹`Average Salaries`,
## # ²`Count of Data Records`, ³`Lowest Salary`, ⁴`Highest Salary`,
## # ⁵`Average Overtime Pay`, ⁶`Salary Gap`stat2019compare <- stat2019 %>%
ggplot(aes(x=`Average Salaries`,
y=`Salary Gap`)) +
geom_point(aes(text = paste("Department: ",`Department`))) +
geom_smooth(method=lm , se = T) +
theme_minimal()
ggplotly(stat2019compare + labs(title = "2019 Average Salary Versus Salary Gap"))2020 Employee Salaries Review
str(db2020)## 'data.frame': 9958 obs. of 9 variables:
## $ Department : chr "ABS" "ABS" "ABS" "ABS" ...
## $ Department Name: chr "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" ...
## $ Division : chr "Wholesale Administration" "Administrative Services" "Administration" "Wholesale Operations" ...
## $ Gender : chr "F" "F" "M" "F" ...
## $ Base Salary : num 78902 35926 167345 90848 78902 ...
## $ Overtime : chr "199.17" NA NA NA ...
## $ Longevity : chr NA NA NA "5717.68" ...
## $ Grade : chr "18" "16" "M2" "21" ...
## $ Year : num 2020 2020 2020 2020 2020 2020 2020 2020 2020 2020 ...options(scipen = 999)
plot2020 <- ggplot(db2020, aes(`Department`, `Base Salary`))+
geom_boxplot() +
scale_fill_viridis(discrete = TRUE, alpha=0.6) +
theme(axis.text.x = element_text(angle = 45),
legend.position="none",
plot.title = element_text(size=11)
) +
ggtitle("2020 Salaries by Department") +
xlab("Department")
ggplotly(plot2020)stat2020 <- db2020 %>%
group_by(Department) %>%
summarise("Total Salaries" = sum(`Base Salary`,na.rm=T),
"Average Salaries" = mean(`Base Salary`,na.rm=T),
"Count of Data Records" = n(),
"Lowest Salary" = min(`Base Salary`,na.rm=T),
"Highest Salary" = max(`Base Salary`,na.rm=T),
"Average Overtime Pay" = mean(`Overtime`, na.rm=T)) %>%
mutate("Salary Gap" = `Highest Salary` - `Lowest Salary`)
head(stat2020)## # A tibble: 6 × 8
## Department `Total Salaries` Average …¹ Count…² Lowes…³ Highe…⁴ Avera…⁵ Salar…⁶
## <chr> <dbl> <dbl> <int> <dbl> <dbl> <dbl> <dbl>
## 1 ABS 24559238. 56071. 438 18574. 220000 NA 201426.
## 2 BOA 282135. 94045. 3 58482. 144751 NA 86269.
## 3 BOE 2373064. 81830. 29 37881. 183654. NA 145773.
## 4 CAT 8547934. 115513. 74 20470. 210143 NA 189673.
## 5 CCL 9442174. 95375. 99 30000 206000 NA 176000
## 6 CEC 5766979. 69482. 83 22971. 186735. NA 163765.
## # … with abbreviated variable names ¹`Average Salaries`,
## # ²`Count of Data Records`, ³`Lowest Salary`, ⁴`Highest Salary`,
## # ⁵`Average Overtime Pay`, ⁶`Salary Gap`stat2020compare <- stat2020 %>%
ggplot(aes(x=`Average Salaries`,
y=`Salary Gap`)) +
geom_point(aes(text = paste("Department: ",`Department`))) +
geom_smooth(method=lm , se = T) +
theme_minimal()
ggplotly(stat2020compare + labs(title = "2020 Average Salary Versus Salary Gap"))2021 Employee Salaries Review
str(db2021)## 'data.frame': 9907 obs. of 9 variables:
## $ Department : chr "ABS" "ABS" "ABS" "ABS" ...
## $ Department Name: chr "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" "Alcohol Beverage Services" ...
## $ Division : chr "Beer Loading" "Liquor and Wine Delivery Operations" "Beer Delivery Operations" "Beer Delivery Operations" ...
## $ Gender : chr "M" "M" "M" "M" ...
## $ Base Salary : num 87969 80086 80086 70814 76419 ...
## $ Overtime : chr "32953.25" NA "31369.15" "29837.85" ...
## $ Longevity : chr NA NA "1591.2" NA ...
## $ Grade : chr "20" "18" "18" "18" ...
## $ Year : num 2021 2021 2021 2021 2021 ...options(scipen = 999)
plot2021 <- ggplot(db2021, aes(`Department`, `Base Salary`))+
geom_boxplot() +
scale_fill_viridis(discrete = TRUE, alpha=0.6) +
theme(axis.text.x = element_text(angle = 45),
legend.position="none",
plot.title = element_text(size=11)
) +
ggtitle("2021 Salaries by Department") +
xlab("Department")
ggplotly(plot2021)stat2021 <- db2021 %>%
group_by(Department) %>%
summarise("Total Salaries" = sum(`Base Salary`,na.rm=T),
"Average Salaries" = mean(`Base Salary`,na.rm=T),
"Count of Data Records" = n(),
"Lowest Salary" = min(`Base Salary`,na.rm=T),
"Highest Salary" = max(`Base Salary`,na.rm=T),
"Average Overtime Pay" = mean(`Overtime`, na.rm=T)) %>%
mutate("Salary Gap" = `Highest Salary` - `Lowest Salary`)
head(stat2021)## # A tibble: 6 × 8
## Department `Total Salaries` Average …¹ Count…² Lowes…³ Highe…⁴ Avera…⁵ Salar…⁶
## <chr> <dbl> <dbl> <int> <dbl> <dbl> <dbl> <dbl>
## 1 ABS 26083216. 57579. 453 18574. 220000 NA 201426.
## 2 BOA 290596 96865. 3 63587 146923 NA 83336
## 3 BOE 2243380. 86284. 26 41703. 189951. NA 148248.
## 4 CAT 9230528. 121454. 76 22202 220000 NA 197798
## 5 CCL 10845916. 99504. 109 17963 209090 NA 191127
## 6 CEC 6866033. 73043. 94 24131. 189536. NA 165405.
## # … with abbreviated variable names ¹`Average Salaries`,
## # ²`Count of Data Records`, ³`Lowest Salary`, ⁴`Highest Salary`,
## # ⁵`Average Overtime Pay`, ⁶`Salary Gap`stat2021compare <- stat2021 %>%
ggplot(aes(x=`Average Salaries`,
y=`Salary Gap`)) +
geom_point(aes(text = paste("Department: ",`Department`))) +
geom_smooth(method=lm , se = T) +
theme_minimal()
ggplotly(stat2021compare + labs(title = "2021 Average Salary Versus Salary Gap"))We see that the 2021 has a lot of na values in the overtime and longevity pay columns. Taking a look into the dataset, we see that it is due to being blank values, rather than entering 0. In the next set we will clean up the dataset.
2022 Employee Salaries Review
str(db2022)## 'data.frame': 10052 obs. of 9 variables:
## $ Department : chr "ZAH" "ZAH" "ZAH" "ZAH" ...
## $ Department Name: chr "Office of Zoning and Administrative Hearings" "Office of Zoning and Administrative Hearings" "Office of Zoning and Administrative Hearings" "Office of Zoning and Administrative Hearings" ...
## $ Division : chr "ZAH 05 Zoning and Administrative Hearings" "ZAH 05 Zoning and Administrative Hearings" "ZAH 05 Zoning and Administrative Hearings" "ZAH 05 Office of Zoning and Administrative Hearings" ...
## $ Gender : chr "M" "F" "F" "F" ...
## $ Base Salary : num 142100 162000 93895 77180 196632 ...
## $ Overtime : chr NA NA NA NA ...
## $ Longevity : chr NA NA NA NA ...
## $ Grade : chr "N35" "N35" "N21" "N18" ...
## $ Year : num 2022 2022 2022 2022 2022 ...options(scipen = 999)
plot2022 <- ggplot(db2022, aes(`Department`, `Base Salary`))+
geom_boxplot() +
scale_fill_viridis(discrete = TRUE, alpha=0.6) +
theme(axis.text.x = element_text(angle = 45),
legend.position="none",
plot.title = element_text(size=11)
) +
ggtitle("2022 Salaries by Department") +
xlab("Department")
ggplotly(plot2022)stat2022 <- db2022 %>%
group_by(Department) %>%
summarise("Total Salaries" = sum(`Base Salary`,na.rm=T),
"Average Salaries" = mean(`Base Salary`,na.rm=T),
"Count of Data Records" = n(),
"Lowest Salary" = min(`Base Salary`,na.rm=T),
"Highest Salary" = max(`Base Salary`,na.rm=T),
"Average Overtime Pay" = mean(`Overtime`, na.rm=T)) %>%
mutate("Salary Gap" = `Highest Salary` - `Lowest Salary`)
head(stat2022)## # A tibble: 6 × 8
## Department `Total Salaries` Average …¹ Count…² Lowes…³ Highe…⁴ Avera…⁵ Salar…⁶
## <chr> <dbl> <dbl> <int> <dbl> <dbl> <dbl> <dbl>
## 1 ABS 26744480. 60100. 445 18854. 220000 NA 201146.
## 2 BOA 297932. 99311. 3 67555. 148607 NA 81052.
## 3 BOE 2391333. 85405. 28 45114. 170000 NA 124886.
## 4 CAT 9517404. 123603. 77 23044 236000 NA 212956
## 5 CCL 12874837. 106404. 121 32842 225725. NA 192883.
## 6 CEC 7081281. 70813. 100 21500 185000 NA 163500
## # … with abbreviated variable names ¹`Average Salaries`,
## # ²`Count of Data Records`, ³`Lowest Salary`, ⁴`Highest Salary`,
## # ⁵`Average Overtime Pay`, ⁶`Salary Gap`stat2022compare <- stat2022 %>%
ggplot(aes(x=`Average Salaries`,
y=`Salary Gap`)) +
geom_point(aes(text = paste("Department: ",`Department`))) +
geom_smooth(method=lm , se = T) +
theme_minimal()
ggplotly(stat2022compare + labs(title = "2022 Average Salary Versus Salary Gap"))Merge all three years together
mergedSalary <- do.call("rbind",list(db2019,db2020,db2021,db2022))
statAllYear <- mergedSalary %>%
group_by(Department, Year) %>%
summarise("Total Salaries" = sum(`Base Salary`,na.rm=T),
"Average Salaries" = mean(`Base Salary`,na.rm=T),
"Count of Data Records" = n(),
"Lowest Salary" = min(`Base Salary`,na.rm=T),
"Highest Salary" = max(`Base Salary`,na.rm=T),
"Average Overtime Pay" = mean(`Overtime`, na.rm=T)) %>%
mutate("Salary Gap" = `Highest Salary` - `Lowest Salary`)
head(statAllYear)## # A tibble: 6 × 9
## # Groups: Department [2]
## Department Year Total Salar…¹ Avera…² Count…³ Lowes…⁴ Highe…⁵ Avera…⁶ Salar…⁷
## <chr> <dbl> <dbl> <dbl> <int> <dbl> <dbl> <dbl> <dbl>
## 1 ABS 2020 24559238. 56071. 438 18574. 220000 NA 201426.
## 2 ABS 2021 26083216. 57579. 453 18574. 220000 NA 201426.
## 3 ABS 2022 26744480. 60100. 445 18854. 220000 NA 201146.
## 4 BOA 2019 282135. 94045. 3 58482. 144751 NA 86269.
## 5 BOA 2020 282135. 94045. 3 58482. 144751 NA 86269.
## 6 BOA 2021 290596 96865. 3 63587 146923 NA 83336
## # … with abbreviated variable names ¹`Total Salaries`, ²`Average Salaries`,
## # ³`Count of Data Records`, ⁴`Lowest Salary`, ⁵`Highest Salary`,
## # ⁶`Average Overtime Pay`, ⁷`Salary Gap`statOnlyYear <- mergedSalary %>%
group_by(Year) %>%
summarise("Total Salaries" = sum(`Base Salary`,na.rm=T),
"Average Salaries" = mean(`Base Salary`,na.rm=T),
"Count of Data Records" = n(),
"Lowest Salary" = min(`Base Salary`,na.rm=T),
"Highest Salary" = max(`Base Salary`,na.rm=T),
"Average Overtime Pay" = mean(`Overtime`, na.rm=T)) %>%
mutate("Salary Gap" = `Highest Salary` - `Lowest Salary`)Review of All Years
# Comparison plots
compareAllp1 <- statAllYear %>%
ggplot(aes(x = `Average Salaries`, y = `Salary Gap`)) +
geom_point(aes(size = `Count of Data Records`,
color = Department,
alpha = 0.6))+
theme_minimal() +
geom_smooth(method = lm, se = T)+
facet_wrap(~Year,nrow=2)+
theme(legend.position='none')+
scale_x_continuous(labels = scales::dollar_format())+
scale_y_continuous(labels = scales::dollar_format())+
labs(title="Salary Gap Versus Average Salary by Year")
ggplotly(compareAllp1)Create Averages
# Create average for each department year
mergedSalaryFinal <- merge(mergedSalary, statAllYear, by = c("Department","Year"))
compareAllp2 <- mergedSalaryFinal %>%
ggplot(aes(x = `Base Salary`, y = `Average Salaries`)) +
geom_point(aes(color = Department,
alpha = 0.6))+
theme_minimal() +
facet_wrap(~Year,nrow=2)+
theme(legend.position='none')+
scale_x_continuous(labels = scales::dollar_format())+
scale_y_continuous(labels = scales::dollar_format())+
labs(title="Base Salary Versus Average Salary Per Year")
# ggplotly(compareAllp2) # too much lag to actual run due to number of items
compareAllp2
# Create average for yearcor(mergedSalaryFinal$`Base Salary`, mergedSalaryFinal$`Average Salaries`)## [1] 0.4388825model <- lm(`Average Salaries`~`Base Salary`, data = mergedSalaryFinal)
summary(model)##
## Call:
## lm(formula = `Average Salaries` ~ `Base Salary`, data = mergedSalaryFinal)
##
## Residuals:
## Min 1Q Median 3Q Max
## -51815 -6956 -281 5610 78351
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 65510.572193 170.977633 383.15 <0.0000000000000002 ***
## `Base Salary` 0.192618 0.001971 97.71 <0.0000000000000002 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 12090 on 40020 degrees of freedom
## Multiple R-squared: 0.1926, Adjusted R-squared: 0.1926
## F-statistic: 9548 on 1 and 40020 DF, p-value: < 0.00000000000000022From our linear regression model between the average salaries and base salary, we find our formula to be:
\[ Average Salaries = 65510.57 + 0.192618 * Base Salary \]
growthRateAVG = statOnlyYear %>%
arrange(Year) %>%
mutate(diffYear = Year - lag(Year),
diffGrowth = `Average Salaries` - lag(`Average Salaries`),
ratePerc= (diffGrowth / diffYear)/lag(`Average Salaries`) * 100)
AvgGrowth = mean(growthRateAVG$ratePerc,na.rm = TRUE)
print(paste("Our average salaries are increasing at a", round(AvgGrowth,2), "% rate."))## [1] "Our average salaries are increasing at a 2.72 % rate."Trend Analysis
# Do some graphs
yearAvgSalPlot <- statOnlyYear %>%
ggplot(aes(x = Year, y = `Average Salaries`)) +
geom_line(color="Orange") +
geom_point() +
theme_minimal() +
labs(title="Montgomery County Trend of Average salaries")
ggsave("MCSal.png")
ggplotly(yearAvgSalPlot)| Year | Average Salary | Percentage Change | Inflation |
|---|---|---|---|
| 2019 | $78,936.02 | 2.3% | |
| 2020 | $78,771.46 | -0.208% | 1.4% |
| 2021 | $81,339.53 | 3.260% | 7.0% |
| 2022 | $85,503.28 | 5.119% | 6.5% |
mu <- mergedSalaryFinal %>%
group_by(Gender) %>%
summarise("grp.mean" = mean(`Average Salaries`,na.rm=T))
yearHistGenderPlot <- mergedSalaryFinal %>%
ggplot(aes(x = `Average Salaries`, color = Gender)) +
geom_histogram(fill = "White",position = "dodge", alpha = 0.5) +
geom_vline(data = mu, aes(xintercept=grp.mean,color=Gender),
linetype = "dashed") +
facet_wrap(~Year,nrow = 2) +
scale_x_continuous(labels = scales::dollar_format()) +
labs(title="Average Salaries by Year and Gender") +
ylab("Number of Employees")
ggsave("MontgomeryYearGender.png")
ggplotly(yearHistGenderPlot)Here we can see in each year, on average females had a higher average salary as compared to their male counterparts.
Review of additional datasets (Final Project)
Due to some limitations with the datasets, we want to pull in outside resources to support statistical analysis. Some of the data points we will be able to use are US Inflation rates, Montgomery County unemployment rates, amongst others.
Employment changes
# Discuss changes in employment counts/avg/std by department# Pull in inflation data
inflation <- data.frame(year=c("2019","2020","2021","2022"),
rate=c(0.023,0.014,0.07,0.065))
# Unemployment Rate for Montgomery County provided by U.S. Bureau of Labor Statistics (BLS)
unemploymentRate <- data.frame(year=c("2019","2020","2021","2022"),
rate=c(0.027,0.054,0.042,0.028))
# discuss Maryland rules on work from home and some timings on major rulingsReview of specific departments
# Break out of departments that may have some interest
# CEX <- Has the largest salary gap
# IGR <- highest average salary
# LIB HRC TBS <- this group has single outlier on top
# POL DOT <- Has large amount of outliers above and belowAnalysis
countyStat2020 <- data.frame(County = c("Montgomery", "Howard", "Fairfax","DC","Loudoun","Santa Clara","Douglas", "Williamson"),
State = c("MD", "MD","VA","DC","VA","CA","CO","TN"),
Population = c(1050000,322407,1150000,701974,405312,1920000,344280,232380),
Employed = c(559029, 170492,611782,382000,220064,992202,188155,117937),
MedianIncome = c(111812,124042,127866,90842,147111,130890,121393,111196),
MedianIncomeMen = c(63611,84366,74391,72201,93579,79036,79055,76666),
MedianIncomeWomen = c(50242,55539,52422,61133,52860,50802,46163,41063))
print(head(countyStat2020,8))## County State Population Employed MedianIncome MedianIncomeMen
## 1 Montgomery MD 1050000 559029 111812 63611
## 2 Howard MD 322407 170492 124042 84366
## 3 Fairfax VA 1150000 611782 127866 74391
## 4 DC DC 701974 382000 90842 72201
## 5 Loudoun VA 405312 220064 147111 93579
## 6 Santa Clara CA 1920000 992202 130890 79036
## 7 Douglas CO 344280 188155 121393 79055
## 8 Williamson TN 232380 117937 111196 76666
## MedianIncomeWomen
## 1 50242
## 2 55539
## 3 52422
## 4 61133
## 5 52860
## 6 50802
## 7 46163
## 8 41063allCounty2018 <- read.csv("2018AllCounty.csv")
allCounty2019 <- read.csv("2019AllCounty.csv")
allCounty2021 <- read.csv("2021AllCounty.csv")
print(head(allCounty2018,20))## Label..Grouping.
## 1 Total
## 2 Less than $10,000
## 3 $10,000 to $14,999
## 4 $15,000 to $24,999
## 5 $25,000 to $34,999
## 6 $35,000 to $49,999
## 7 $50,000 to $74,999
## 8 $75,000 to $99,999
## 9 $100,000 to $149,999
## 10 $150,000 to $199,999
## 11 $200,000 or more
## 12 Median income (dollars)
## 13 Mean income (dollars)
## 14 PERCENT ALLOCATED
## 15 Household income in the past 12 months
## 16 Family income in the past 12 months
## 17 Nonfamily income in the past 12 months
## Santa.Clara.County..California..Households..Estimate
## 1 645,108
## 2 3.2%
## 3 2.4%
## 4 4.4%
## 5 4.0%
## 6 6.2%
## 7 10.7%
## 8 9.6%
## 9 17.0%
## 10 13.0%
## 11 29.5%
## 12 126,606
## 13 170,966
## 14
## 15 32.5%
## 16 (X)
## 17 (X)
## Santa.Clara.County..California..Households..Margin.of.Error
## 1 ±4,162
## 2 ±0.3
## 3 ±0.3
## 4 ±0.4
## 5 ±0.5
## 6 ±0.5
## 7 ±0.6
## 8 ±0.6
## 9 ±0.7
## 10 ±0.6
## 11 ±0.9
## 12 ±3,046
## 13 ±2,944
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Santa.Clara.County..California..Families..Estimate
## 1 461,799
## 2 1.6%
## 3 1.2%
## 4 3.1%
## 5 3.1%
## 6 5.7%
## 7 9.9%
## 8 9.3%
## 9 17.4%
## 10 14.4%
## 11 34.3%
## 12 144,690
## 13 191,428
## 14
## 15 (X)
## 16 32.9%
## 17 (X)
## Santa.Clara.County..California..Families..Margin.of.Error
## 1 ±6,200
## 2 ±0.3
## 3 ±0.3
## 4 ±0.4
## 5 ±0.5
## 6 ±0.5
## 7 ±0.6
## 8 ±0.6
## 9 ±0.9
## 10 ±0.7
## 11 ±1.0
## 12 ±3,621
## 13 ±3,618
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Santa.Clara.County..California..Married.couple.families..Estimate
## 1 368,382
## 2 0.9%
## 3 0.8%
## 4 2.1%
## 5 2.1%
## 6 4.6%
## 7 7.8%
## 8 8.2%
## 9 17.6%
## 10 15.7%
## 11 40.1%
## 12 165,859
## 13 213,957
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Santa.Clara.County..California..Married.couple.families..Margin.of.Error
## 1 ±7,413
## 2 ±0.2
## 3 ±0.3
## 4 ±0.4
## 5 ±0.4
## 6 ±0.5
## 7 ±0.7
## 8 ±0.6
## 9 ±1.1
## 10 ±0.9
## 11 ±1.3
## 12 ±4,493
## 13 ±4,692
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Santa.Clara.County..California..Nonfamily.households..Estimate
## 1 183,309
## 2 7.6%
## 3 5.7%
## 4 8.4%
## 5 6.7%
## 6 8.4%
## 7 12.6%
## 8 10.7%
## 9 15.8%
## 10 8.9%
## 11 15.1%
## 12 76,088
## 13 111,119
## 14
## 15 (X)
## 16 (X)
## 17 29.1%
## Santa.Clara.County..California..Nonfamily.households..Margin.of.Error
## 1 ±5,758
## 2 ±1.0
## 3 ±0.9
## 4 ±1.1
## 5 ±1.0
## 6 ±1.1
## 7 ±1.3
## 8 ±1.2
## 9 ±1.5
## 10 ±1.2
## 11 ±1.4
## 12 ±4,157
## 13 ±4,503
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Douglas.County..Colorado..Households..Estimate
## 1 125,035
## 2 1.4%
## 3 1.1%
## 4 2.7%
## 5 4.1%
## 6 5.7%
## 7 12.5%
## 8 12.0%
## 9 23.3%
## 10 16.1%
## 11 21.2%
## 12 119,615
## 13 145,975
## 14
## 15 27.1%
## 16 (X)
## 17 (X)
## Douglas.County..Colorado..Households..Margin.of.Error
## 1 ±1,382
## 2 ±0.4
## 3 ±0.4
## 4 ±0.7
## 5 ±0.9
## 6 ±1.2
## 7 ±1.6
## 8 ±1.3
## 9 ±2.0
## 10 ±1.4
## 11 ±1.8
## 12 ±3,572
## 13 ±5,061
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Douglas.County..Colorado..Families..Estimate
## 1 95,944
## 2 0.7%
## 3 0.3%
## 4 1.2%
## 5 2.0%
## 6 3.7%
## 7 9.7%
## 8 12.3%
## 9 25.5%
## 10 19.2%
## 11 25.6%
## 12 138,080
## 13 163,619
## 14
## 15 (X)
## 16 27.6%
## 17 (X)
## Douglas.County..Colorado..Families..Margin.of.Error
## 1 ±2,308
## 2 ±0.4
## 3 ±0.2
## 4 ±0.5
## 5 ±0.7
## 6 ±1.0
## 7 ±1.4
## 8 ±1.5
## 9 ±2.4
## 10 ±1.8
## 11 ±2.3
## 12 ±6,737
## 13 ±6,154
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Douglas.County..Colorado..Married.couple.families..Estimate
## 1 82,851
## 2 0.4%
## 3 0.2%
## 4 0.5%
## 5 1.2%
## 6 2.5%
## 7 8.1%
## 8 11.9%
## 9 25.3%
## 10 21.2%
## 11 28.6%
## 12 149,397
## 13 N
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Douglas.County..Colorado..Married.couple.families..Margin.of.Error
## 1 ±2,661
## 2 ±0.3
## 3 ±0.2
## 4 ±0.4
## 5 ±0.5
## 6 ±0.8
## 7 ±1.3
## 8 ±1.6
## 9 ±2.3
## 10 ±2.1
## 11 ±2.6
## 12 ±6,629
## 13 N
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Douglas.County..Colorado..Nonfamily.households..Estimate
## 1 29,091
## 2 3.9%
## 3 3.6%
## 4 7.9%
## 5 11.0%
## 6 13.1%
## 7 24.2%
## 8 11.7%
## 9 15.4%
## 10 3.6%
## 11 5.5%
## 12 57,487
## 13 82,064
## 14
## 15 (X)
## 16 (X)
## 17 23.0%
## Douglas.County..Colorado..Nonfamily.households..Margin.of.Error
## 1 ±2,248
## 2 ±1.4
## 3 ±1.7
## 4 ±2.5
## 5 ±3.1
## 6 ±3.2
## 7 ±4.7
## 8 ±2.6
## 9 ±3.5
## 10 ±1.8
## 11 ±1.7
## 12 ±5,991
## 13 ±7,036
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## District.of.Columbia..District.of.Columbia..Households..Estimate
## 1 287,476
## 2 9.3%
## 3 4.6%
## 4 5.4%
## 5 5.9%
## 6 7.8%
## 7 12.2%
## 8 10.7%
## 9 16.5%
## 10 9.1%
## 11 18.4%
## 12 85,203
## 13 124,946
## 14
## 15 28.2%
## 16 (X)
## 17 (X)
## District.of.Columbia..District.of.Columbia..Households..Margin.of.Error
## 1 ±2,752
## 2 ±0.9
## 3 ±0.7
## 4 ±0.7
## 5 ±0.8
## 6 ±1.1
## 7 ±1.0
## 8 ±1.0
## 9 ±0.9
## 10 ±0.7
## 11 ±1.0
## 12 ±3,414
## 13 ±3,134
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## District.of.Columbia..District.of.Columbia..Families..Estimate
## 1 121,529
## 2 6.3%
## 3 2.8%
## 4 5.4%
## 5 5.3%
## 6 6.1%
## 7 11.3%
## 8 8.3%
## 9 13.5%
## 10 10.6%
## 11 30.3%
## 12 117,713
## 13 165,174
## 14
## 15 (X)
## 16 32.4%
## 17 (X)
## District.of.Columbia..District.of.Columbia..Families..Margin.of.Error
## 1 ±3,987
## 2 ±1.3
## 3 ±0.8
## 4 ±1.1
## 5 ±1.2
## 6 ±1.3
## 7 ±1.5
## 8 ±1.2
## 9 ±1.5
## 10 ±1.3
## 11 ±1.9
## 12 ±9,669
## 13 ±6,305
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## District.of.Columbia..District.of.Columbia..Married.couple.families..Estimate
## 1 73,009
## 2 0.8%
## 3 0.5%
## 4 2.2%
## 5 3.0%
## 6 4.6%
## 7 6.9%
## 8 6.7%
## 9 14.2%
## 10 13.9%
## 11 47.2%
## 12 189,213
## 13 227,903
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## District.of.Columbia..District.of.Columbia..Married.couple.families..Margin.of.Error
## 1 ±3,036
## 2 ±0.4
## 3 ±0.4
## 4 ±0.8
## 5 ±0.9
## 6 ±1.5
## 7 ±1.6
## 8 ±1.4
## 9 ±2.0
## 10 ±1.8
## 11 ±2.9
## 12 ±13,371
## 13 ±9,379
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## District.of.Columbia..District.of.Columbia..Nonfamily.households..Estimate
## 1 165,947
## 2 12.1%
## 3 6.0%
## 4 5.4%
## 5 6.4%
## 6 9.1%
## 7 13.1%
## 8 12.7%
## 9 18.0%
## 10 8.2%
## 11 8.9%
## 12 71,189
## 13 92,251
## 14
## 15 (X)
## 16 (X)
## 17 23.8%
## District.of.Columbia..District.of.Columbia..Nonfamily.households..Margin.of.Error
## 1 ±4,916
## 2 ±1.3
## 3 ±1.1
## 4 ±1.0
## 5 ±1.1
## 6 ±1.4
## 7 ±1.4
## 8 ±1.2
## 9 ±1.4
## 10 ±0.9
## 11 ±1.0
## 12 ±2,664
## 13 ±3,501
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Howard.County..Maryland..Households..Estimate
## 1 116,903
## 2 2.5%
## 3 2.2%
## 4 2.8%
## 5 4.3%
## 6 6.3%
## 7 12.0%
## 8 12.4%
## 9 18.7%
## 10 15.1%
## 11 23.8%
## 12 116,984
## 13 144,344
## 14
## 15 26.2%
## 16 (X)
## 17 (X)
## Howard.County..Maryland..Households..Margin.of.Error
## 1 ±1,234
## 2 ±0.7
## 3 ±0.8
## 4 ±1.0
## 5 ±1.0
## 6 ±1.2
## 7 ±1.8
## 8 ±1.4
## 9 ±1.7
## 10 ±1.7
## 11 ±2.0
## 12 ±5,415
## 13 ±5,449
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Howard.County..Maryland..Families..Estimate
## 1 86,143
## 2 2.0%
## 3 1.1%
## 4 1.5%
## 5 3.0%
## 6 4.5%
## 7 8.1%
## 8 11.8%
## 9 19.6%
## 10 17.5%
## 11 30.9%
## 12 146,269
## 13 167,421
## 14
## 15 (X)
## 16 27.0%
## 17 (X)
## Howard.County..Maryland..Families..Margin.of.Error
## 1 ±2,555
## 2 ±0.8
## 3 ±0.5
## 4 ±0.8
## 5 ±1.0
## 6 ±1.3
## 7 ±1.7
## 8 ±1.7
## 9 ±2.0
## 10 ±2.1
## 11 ±2.5
## 12 ±7,082
## 13 ±6,854
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Howard.County..Maryland..Married.couple.families..Estimate
## 1 70,178
## 2 1.0%
## 3 0.6%
## 4 1.0%
## 5 1.1%
## 6 4.5%
## 7 6.3%
## 8 10.2%
## 9 19.4%
## 10 19.9%
## 11 36.0%
## 12 160,837
## 13 184,742
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Howard.County..Maryland..Married.couple.families..Margin.of.Error
## 1 ±2,554
## 2 ±0.5
## 3 ±0.4
## 4 ±0.7
## 5 ±0.6
## 6 ±1.5
## 7 ±1.5
## 8 ±1.7
## 9 ±2.1
## 10 ±2.4
## 11 ±2.7
## 12 ±5,511
## 13 ±6,813
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Howard.County..Maryland..Nonfamily.households..Estimate
## 1 30,760
## 2 5.1%
## 3 5.7%
## 4 6.1%
## 5 7.4%
## 6 11.3%
## 7 22.6%
## 8 14.4%
## 9 16.4%
## 10 8.4%
## 11 2.7%
## 12 62,120
## 13 75,764
## 14
## 15 (X)
## 16 (X)
## 17 24.0%
## Howard.County..Maryland..Nonfamily.households..Margin.of.Error
## 1 ±2,436
## 2 ±1.7
## 3 ±2.5
## 4 ±2.6
## 5 ±2.8
## 6 ±2.8
## 7 ±4.1
## 8 ±3.4
## 9 ±3.6
## 10 ±2.8
## 11 ±1.4
## 12 ±5,557
## 13 ±5,129
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Montgomery.County..Maryland..Households..Estimate
## 1 368,334
## 2 3.6%
## 3 2.1%
## 4 3.9%
## 5 4.6%
## 6 7.0%
## 7 12.4%
## 8 12.2%
## 9 20.1%
## 10 12.0%
## 11 22.0%
## 12 108,188
## 13 147,917
## 14
## 15 28.4%
## 16 (X)
## 17 (X)
## Montgomery.County..Maryland..Households..Margin.of.Error
## 1 ±3,409
## 2 ±0.5
## 3 ±0.4
## 4 ±0.5
## 5 ±0.6
## 6 ±0.7
## 7 ±0.8
## 8 ±0.8
## 9 ±0.9
## 10 ±0.7
## 11 ±0.9
## 12 ±2,782
## 13 ±3,706
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Montgomery.County..Maryland..Families..Estimate
## 1 257,291
## 2 1.5%
## 3 1.1%
## 4 3.1%
## 5 4.0%
## 6 5.8%
## 7 10.9%
## 8 10.5%
## 9 21.6%
## 10 13.2%
## 11 28.2%
## 12 127,733
## 13 170,020
## 14
## 15 (X)
## 16 28.6%
## 17 (X)
## Montgomery.County..Maryland..Families..Margin.of.Error
## 1 ±4,245
## 2 ±0.4
## 3 ±0.4
## 4 ±0.7
## 5 ±0.7
## 6 ±0.7
## 7 ±0.9
## 8 ±0.8
## 9 ±1.3
## 10 ±0.8
## 11 ±1.3
## 12 ±3,756
## 13 ±5,147
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Montgomery.County..Maryland..Married.couple.families..Estimate
## 1 203,585
## 2 0.5%
## 3 0.8%
## 4 2.0%
## 5 2.8%
## 6 4.3%
## 7 9.2%
## 8 10.0%
## 9 21.6%
## 10 14.6%
## 11 34.2%
## 12 145,586
## 13 190,645
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Montgomery.County..Maryland..Married.couple.families..Margin.of.Error
## 1 ±4,499
## 2 ±0.2
## 3 ±0.3
## 4 ±0.5
## 5 ±0.6
## 6 ±0.7
## 7 ±1.0
## 8 ±0.9
## 9 ±1.4
## 10 ±1.0
## 11 ±1.6
## 12 ±6,049
## 13 ±6,051
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Montgomery.County..Maryland..Nonfamily.households..Estimate
## 1 111,043
## 2 8.9%
## 3 4.4%
## 4 6.9%
## 5 6.3%
## 6 10.3%
## 7 16.2%
## 8 16.1%
## 9 16.2%
## 10 7.9%
## 11 6.8%
## 12 70,363
## 13 91,848
## 14
## 15 (X)
## 16 (X)
## 17 25.3%
## Montgomery.County..Maryland..Nonfamily.households..Margin.of.Error
## 1 ±4,292
## 2 ±1.4
## 3 ±1.1
## 4 ±1.3
## 5 ±1.1
## 6 ±1.5
## 7 ±1.7
## 8 ±1.9
## 9 ±1.6
## 10 ±1.3
## 11 ±1.2
## 12 ±2,901
## 13 ±5,008
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Williamson.County..Tennessee..Households..Estimate
## 1 80,601
## 2 2.1%
## 3 1.3%
## 4 3.3%
## 5 3.8%
## 6 7.8%
## 7 14.5%
## 8 11.0%
## 9 23.7%
## 10 12.2%
## 11 20.3%
## 12 110,700
## 13 144,348
## 14
## 15 30.7%
## 16 (X)
## 17 (X)
## Williamson.County..Tennessee..Households..Margin.of.Error
## 1 ±1,433
## 2 ±0.9
## 3 ±0.7
## 4 ±1.5
## 5 ±1.4
## 6 ±1.8
## 7 ±2.1
## 8 ±2.1
## 9 ±2.9
## 10 ±1.8
## 11 ±2.2
## 12 ±7,754
## 13 ±6,643
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Williamson.County..Tennessee..Families..Estimate
## 1 63,944
## 2 0.8%
## 3 1.1%
## 4 1.4%
## 5 2.0%
## 6 6.0%
## 7 13.2%
## 8 11.8%
## 9 25.2%
## 10 14.3%
## 11 24.1%
## 12 125,976
## 13 161,875
## 14
## 15 (X)
## 16 29.6%
## 17 (X)
## Williamson.County..Tennessee..Families..Margin.of.Error
## 1 ±2,360
## 2 ±0.5
## 3 ±0.9
## 4 ±0.7
## 5 ±1.1
## 6 ±1.7
## 7 ±2.2
## 8 ±2.4
## 9 ±3.0
## 10 ±2.0
## 11 ±2.5
## 12 ±4,244
## 13 ±7,937
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Williamson.County..Tennessee..Married.couple.families..Estimate
## 1 N
## 2 N
## 3 N
## 4 N
## 5 N
## 6 N
## 7 N
## 8 N
## 9 N
## 10 N
## 11 N
## 12 131,825
## 13 N
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Williamson.County..Tennessee..Married.couple.families..Margin.of.Error
## 1 N
## 2 N
## 3 N
## 4 N
## 5 N
## 6 N
## 7 N
## 8 N
## 9 N
## 10 N
## 11 N
## 12 ±7,818
## 13 N
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Williamson.County..Tennessee..Nonfamily.households..Estimate
## 1 16,657
## 2 7.3%
## 3 3.3%
## 4 11.3%
## 5 11.0%
## 6 16.1%
## 7 18.8%
## 8 9.0%
## 9 14.2%
## 10 4.2%
## 11 4.9%
## 12 51,158
## 13 72,964
## 14
## 15 (X)
## 16 (X)
## 17 34.5%
## Williamson.County..Tennessee..Nonfamily.households..Margin.of.Error
## 1 ±2,166
## 2 ±4.0
## 3 ±1.7
## 4 ±5.9
## 5 ±5.5
## 6 ±5.5
## 7 ±5.4
## 8 ±4.7
## 9 ±7.5
## 10 ±2.4
## 11 ±3.3
## 12 ±9,075
## 13 ±12,942
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Fairfax.County..Virginia..Households..Estimate
## 1 396,628
## 2 2.6%
## 3 1.3%
## 4 3.6%
## 5 3.8%
## 6 6.3%
## 7 11.0%
## 8 11.6%
## 9 20.1%
## 10 14.5%
## 11 25.2%
## 12 122,227
## 13 156,432
## 14
## 15 26.2%
## 16 (X)
## 17 (X)
## Fairfax.County..Virginia..Households..Margin.of.Error
## 1 ±2,777
## 2 ±0.5
## 3 ±0.3
## 4 ±0.5
## 5 ±0.5
## 6 ±0.7
## 7 ±0.7
## 8 ±0.7
## 9 ±0.9
## 10 ±0.9
## 11 ±0.9
## 12 ±2,180
## 13 ±3,048
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Fairfax.County..Virginia..Families..Estimate
## 1 284,876
## 2 1.1%
## 3 1.0%
## 4 2.8%
## 5 3.0%
## 6 5.6%
## 7 9.2%
## 8 10.5%
## 9 19.0%
## 10 16.3%
## 11 31.4%
## 12 142,961
## 13 176,372
## 14
## 15 (X)
## 16 26.3%
## 17 (X)
## Fairfax.County..Virginia..Families..Margin.of.Error
## 1 ±4,850
## 2 ±0.3
## 3 ±0.3
## 4 ±0.6
## 5 ±0.5
## 6 ±0.7
## 7 ±0.8
## 8 ±0.9
## 9 ±1.0
## 10 ±1.1
## 11 ±1.2
## 12 ±3,682
## 13 ±3,840
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Fairfax.County..Virginia..Married.couple.families..Estimate
## 1 231,255
## 2 0.5%
## 3 0.6%
## 4 1.3%
## 5 1.7%
## 6 4.5%
## 7 7.9%
## 8 9.6%
## 9 19.1%
## 10 18.0%
## 11 36.8%
## 12 162,269
## 13 195,259
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Fairfax.County..Virginia..Married.couple.families..Margin.of.Error
## 1 ±5,038
## 2 ±0.2
## 3 ±0.3
## 4 ±0.3
## 5 ±0.4
## 6 ±0.7
## 7 ±0.8
## 8 ±1.0
## 9 ±1.0
## 10 ±1.3
## 11 ±1.5
## 12 ±3,337
## 13 ±4,063
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Fairfax.County..Virginia..Nonfamily.households..Estimate
## 1 111,752
## 2 6.7%
## 3 2.4%
## 4 5.9%
## 5 6.0%
## 6 8.8%
## 7 16.8%
## 8 14.8%
## 9 21.3%
## 10 9.5%
## 11 7.7%
## 12 79,269
## 13 98,979
## 14
## 15 (X)
## 16 (X)
## 17 23.8%
## Fairfax.County..Virginia..Nonfamily.households..Margin.of.Error
## 1 ±4,501
## 2 ±1.3
## 3 ±0.8
## 4 ±1.2
## 5 ±1.2
## 6 ±1.3
## 7 ±1.4
## 8 ±1.5
## 9 ±2.0
## 10 ±1.1
## 11 ±1.2
## 12 ±3,335
## 13 ±4,172
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Loudoun.County..Virginia..Households..Estimate
## 1 133,417
## 2 1.1%
## 3 0.8%
## 4 2.5%
## 5 3.6%
## 6 5.6%
## 7 9.9%
## 8 10.2%
## 9 19.9%
## 10 15.5%
## 11 30.9%
## 12 139,915
## 13 171,772
## 14
## 15 29.9%
## 16 (X)
## 17 (X)
## Loudoun.County..Virginia..Households..Margin.of.Error
## 1 ±1,079
## 2 ±0.4
## 3 ±0.3
## 4 ±0.7
## 5 ±1.0
## 6 ±1.1
## 7 ±1.4
## 8 ±1.3
## 9 ±1.6
## 10 ±1.5
## 11 ±2.0
## 12 ±4,772
## 13 ±7,555
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Loudoun.County..Virginia..Families..Estimate
## 1 103,064
## 2 0.8%
## 3 0.7%
## 4 1.4%
## 5 2.7%
## 6 4.6%
## 7 7.7%
## 8 8.5%
## 9 19.2%
## 10 17.4%
## 11 37.0%
## 12 161,683
## 13 190,514
## 14
## 15 (X)
## 16 30.1%
## 17 (X)
## Loudoun.County..Virginia..Families..Margin.of.Error
## 1 ±2,188
## 2 ±0.4
## 3 ±0.5
## 4 ±0.6
## 5 ±1.1
## 6 ±1.2
## 7 ±1.3
## 8 ±1.4
## 9 ±1.9
## 10 ±1.7
## 11 ±2.4
## 12 ±4,211
## 13 ±9,638
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Loudoun.County..Virginia..Married.couple.families..Estimate
## 1 91,003
## 2 0.3%
## 3 0.3%
## 4 0.6%
## 5 2.0%
## 6 4.0%
## 7 6.6%
## 8 7.6%
## 9 19.4%
## 10 18.4%
## 11 40.8%
## 12 172,241
## 13 N
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Loudoun.County..Virginia..Married.couple.families..Margin.of.Error
## 1 ±2,635
## 2 ±0.3
## 3 ±0.2
## 4 ±0.4
## 5 ±1.1
## 6 ±1.2
## 7 ±1.4
## 8 ±1.5
## 9 ±2.0
## 10 ±1.9
## 11 ±2.5
## 12 ±5,831
## 13 N
## 14
## 15 (X)
## 16 (X)
## 17 (X)
## Loudoun.County..Virginia..Nonfamily.households..Estimate
## 1 30,353
## 2 3.0%
## 3 1.9%
## 4 6.2%
## 5 6.7%
## 6 8.6%
## 7 18.5%
## 8 15.8%
## 9 23.0%
## 10 9.1%
## 11 7.1%
## 12 83,281
## 13 99,433
## 14
## 15 (X)
## 16 (X)
## 17 27.3%
## Loudoun.County..Virginia..Nonfamily.households..Margin.of.Error
## 1 ±2,197
## 2 ±1.6
## 3 ±1.2
## 4 ±2.1
## 5 ±2.9
## 6 ±2.5
## 7 ±3.9
## 8 ±3.1
## 9 ±3.6
## 10 ±2.8
## 11 ±2.3
## 12 ±8,026
## 13 ±7,953
## 14
## 15 (X)
## 16 (X)
## 17 (X)allCounty2018 <- allCounty2018 %>% filter(row_number() %in% c(12,13))
allCounty2019 <- allCounty2019 %>% filter(row_number() %in% c(12,13))
allCounty2021 <- allCounty2021 %>% filter(row_number() %in% c(12,13))
print(head(allCounty2018,20))## Label..Grouping. Santa.Clara.County..California..Households..Estimate
## 1 Median income (dollars) 126,606
## 2 Mean income (dollars) 170,966
## Santa.Clara.County..California..Households..Margin.of.Error
## 1 ±3,046
## 2 ±2,944
## Santa.Clara.County..California..Families..Estimate
## 1 144,690
## 2 191,428
## Santa.Clara.County..California..Families..Margin.of.Error
## 1 ±3,621
## 2 ±3,618
## Santa.Clara.County..California..Married.couple.families..Estimate
## 1 165,859
## 2 213,957
## Santa.Clara.County..California..Married.couple.families..Margin.of.Error
## 1 ±4,493
## 2 ±4,692
## Santa.Clara.County..California..Nonfamily.households..Estimate
## 1 76,088
## 2 111,119
## Santa.Clara.County..California..Nonfamily.households..Margin.of.Error
## 1 ±4,157
## 2 ±4,503
## Douglas.County..Colorado..Households..Estimate
## 1 119,615
## 2 145,975
## Douglas.County..Colorado..Households..Margin.of.Error
## 1 ±3,572
## 2 ±5,061
## Douglas.County..Colorado..Families..Estimate
## 1 138,080
## 2 163,619
## Douglas.County..Colorado..Families..Margin.of.Error
## 1 ±6,737
## 2 ±6,154
## Douglas.County..Colorado..Married.couple.families..Estimate
## 1 149,397
## 2 N
## Douglas.County..Colorado..Married.couple.families..Margin.of.Error
## 1 ±6,629
## 2 N
## Douglas.County..Colorado..Nonfamily.households..Estimate
## 1 57,487
## 2 82,064
## Douglas.County..Colorado..Nonfamily.households..Margin.of.Error
## 1 ±5,991
## 2 ±7,036
## District.of.Columbia..District.of.Columbia..Households..Estimate
## 1 85,203
## 2 124,946
## District.of.Columbia..District.of.Columbia..Households..Margin.of.Error
## 1 ±3,414
## 2 ±3,134
## District.of.Columbia..District.of.Columbia..Families..Estimate
## 1 117,713
## 2 165,174
## District.of.Columbia..District.of.Columbia..Families..Margin.of.Error
## 1 ±9,669
## 2 ±6,305
## District.of.Columbia..District.of.Columbia..Married.couple.families..Estimate
## 1 189,213
## 2 227,903
## District.of.Columbia..District.of.Columbia..Married.couple.families..Margin.of.Error
## 1 ±13,371
## 2 ±9,379
## District.of.Columbia..District.of.Columbia..Nonfamily.households..Estimate
## 1 71,189
## 2 92,251
## District.of.Columbia..District.of.Columbia..Nonfamily.households..Margin.of.Error
## 1 ±2,664
## 2 ±3,501
## Howard.County..Maryland..Households..Estimate
## 1 116,984
## 2 144,344
## Howard.County..Maryland..Households..Margin.of.Error
## 1 ±5,415
## 2 ±5,449
## Howard.County..Maryland..Families..Estimate
## 1 146,269
## 2 167,421
## Howard.County..Maryland..Families..Margin.of.Error
## 1 ±7,082
## 2 ±6,854
## Howard.County..Maryland..Married.couple.families..Estimate
## 1 160,837
## 2 184,742
## Howard.County..Maryland..Married.couple.families..Margin.of.Error
## 1 ±5,511
## 2 ±6,813
## Howard.County..Maryland..Nonfamily.households..Estimate
## 1 62,120
## 2 75,764
## Howard.County..Maryland..Nonfamily.households..Margin.of.Error
## 1 ±5,557
## 2 ±5,129
## Montgomery.County..Maryland..Households..Estimate
## 1 108,188
## 2 147,917
## Montgomery.County..Maryland..Households..Margin.of.Error
## 1 ±2,782
## 2 ±3,706
## Montgomery.County..Maryland..Families..Estimate
## 1 127,733
## 2 170,020
## Montgomery.County..Maryland..Families..Margin.of.Error
## 1 ±3,756
## 2 ±5,147
## Montgomery.County..Maryland..Married.couple.families..Estimate
## 1 145,586
## 2 190,645
## Montgomery.County..Maryland..Married.couple.families..Margin.of.Error
## 1 ±6,049
## 2 ±6,051
## Montgomery.County..Maryland..Nonfamily.households..Estimate
## 1 70,363
## 2 91,848
## Montgomery.County..Maryland..Nonfamily.households..Margin.of.Error
## 1 ±2,901
## 2 ±5,008
## Williamson.County..Tennessee..Households..Estimate
## 1 110,700
## 2 144,348
## Williamson.County..Tennessee..Households..Margin.of.Error
## 1 ±7,754
## 2 ±6,643
## Williamson.County..Tennessee..Families..Estimate
## 1 125,976
## 2 161,875
## Williamson.County..Tennessee..Families..Margin.of.Error
## 1 ±4,244
## 2 ±7,937
## Williamson.County..Tennessee..Married.couple.families..Estimate
## 1 131,825
## 2 N
## Williamson.County..Tennessee..Married.couple.families..Margin.of.Error
## 1 ±7,818
## 2 N
## Williamson.County..Tennessee..Nonfamily.households..Estimate
## 1 51,158
## 2 72,964
## Williamson.County..Tennessee..Nonfamily.households..Margin.of.Error
## 1 ±9,075
## 2 ±12,942
## Fairfax.County..Virginia..Households..Estimate
## 1 122,227
## 2 156,432
## Fairfax.County..Virginia..Households..Margin.of.Error
## 1 ±2,180
## 2 ±3,048
## Fairfax.County..Virginia..Families..Estimate
## 1 142,961
## 2 176,372
## Fairfax.County..Virginia..Families..Margin.of.Error
## 1 ±3,682
## 2 ±3,840
## Fairfax.County..Virginia..Married.couple.families..Estimate
## 1 162,269
## 2 195,259
## Fairfax.County..Virginia..Married.couple.families..Margin.of.Error
## 1 ±3,337
## 2 ±4,063
## Fairfax.County..Virginia..Nonfamily.households..Estimate
## 1 79,269
## 2 98,979
## Fairfax.County..Virginia..Nonfamily.households..Margin.of.Error
## 1 ±3,335
## 2 ±4,172
## Loudoun.County..Virginia..Households..Estimate
## 1 139,915
## 2 171,772
## Loudoun.County..Virginia..Households..Margin.of.Error
## 1 ±4,772
## 2 ±7,555
## Loudoun.County..Virginia..Families..Estimate
## 1 161,683
## 2 190,514
## Loudoun.County..Virginia..Families..Margin.of.Error
## 1 ±4,211
## 2 ±9,638
## Loudoun.County..Virginia..Married.couple.families..Estimate
## 1 172,241
## 2 N
## Loudoun.County..Virginia..Married.couple.families..Margin.of.Error
## 1 ±5,831
## 2 N
## Loudoun.County..Virginia..Nonfamily.households..Estimate
## 1 83,281
## 2 99,433
## Loudoun.County..Virginia..Nonfamily.households..Margin.of.Error
## 1 ±8,026
## 2 ±7,953totalCountyStat <- data.frame(County = c("Montgomery", "Montgomery","Montgomery","Montgomery","Howard","Howard","Howard","Howard", "Fairfax","Fairfax","Fairfax","Fairfax","DC","DC","DC","DC","Loudoun","Loudoun","Loudoun","Loudoun","Santa Clara","Santa Clara","Santa Clara","Santa Clara","Douglas","Douglas","Douglas","Douglas", "Williamson","Williamson","Williamson","Williamson"),
Year = c(2018,2019,2020,2021,2018,2019,2020,2021,2018,2019,2020,2021,2018,2019,2020,2021,2018,2019,2020,2021,2018,2019,2020,2021,2018,2019,2020,2021,2018,2019,2020,2021),
MedianIncome = c(108188,110389,111812,112854,116984,121618,124042,133267,122227,128374,127866,134115,85203,92266,90842,90088,139915,151800,147111,153506,126606,133076,130890,141562,119615,122867,121393,129299,110700,115507,111196,117927))yearMedianAllCountyPlot <- totalCountyStat %>%
ggplot(aes(x = Year, y = `MedianIncome`, color = County)) +
geom_point() +
theme_minimal() +
labs(title="Trend of Median salaries")
ggplotly(yearMedianAllCountyPlot)yearMedianAllCountyPlot <- totalCountyStat %>%
ggplot(aes(x = Year, y = `MedianIncome`)) +
geom_point() +
geom_line(aes(color = County)) +
scale_color_brewer(palette = "Set2") +
theme_minimal() +
labs(title="Trend of Median salaries")+
scale_y_continuous(labels = scales::dollar_format())
yearMedianAllCountyPlot
ggsave("AllCounty.png")Summary of Findings
Source
Deloitte (2020). Data USA County Profile 2020, Data USA. https://datausa.io/
Luh, Kathy (2020). Employee Salaries - 2019, dataMontgomery. https://data.montgomerycountymd.gov/Human-Resources/Employee-Salaries-2019/qatd-z57d
Luh, Kathy (2021). Employee Salaries - 2020, dataMontgomery. https://data.montgomerycountymd.gov/Human-Resources/Employee-Salaries-2020/he7s-ebwb
Luh, Kathy (2022). Employee Salaries - 2021, dataMontgomery. https://data.montgomerycountymd.gov/Human-Resources/Employee-Salaries-2021/kmkb-bmhe
Luh, Kathy (2023). Employee Salaries - 2022, dataMontgomery. https://data.montgomerycountymd.gov/Human-Resources/Employee-Salaries-2022/njz9-yp4y
U.S. Bureau of Labor Statistics, Unemployment Rate in Montgomery County, MD [MDMONT0URN], retrieved from FRED, Federal Reserve Bank of St. Louis; https://fred.stlouisfed.org/series/MDMONT0URN, March 22, 2023.
“CPI Inflation Calculator.” U.S. Bureau of Labor Statistics, https://www.bls.gov/data/inflation_calculator.htm, March 22, 2023.